# _*_ coding:utf-8 _*_
# @Time  : 2022.08.19
# @Author: zizlee


# 统计各个工作指标的数量(首页统计总表)
import datetime
from collections import Counter
from fastapi import APIRouter, Header, Query
from hutool import security
from db import FAConnection
from v1_all_api import constant
from v1_all_api.all_utils import datetime_utils
from v1_all_api.all_response import AllResponse

from . import work_utils

statistics_api = APIRouter()


def calculate_levels(records, user_key, level_key):  # 计算各用户的ABC等级情况
    temp_dict = {}
    for row in records:
        if row[user_key] in temp_dict.keys():
            temp_dict[row[user_key]].append(row[level_key])
        else:
            temp_dict[row[user_key]] = [row[level_key]]
    for u, lv in temp_dict.items():
        lv.sort()
        level_count = Counter(lv).items()
        temp_dict[u] = ''.join(['%d%s' % (v, k) for k, v in level_count if k])
    return temp_dict


def calculate_levels_sum(list_level: list):
    container = []
    for s in list_level:
        if not s:
            continue
        for cr in ['A', 'B', 'C', 'D', 'E', 'F']:
            s = s.replace(cr, f'*{cr}-')
        for sub in s.split('-'):
            if not sub:
                continue
            sl = sub.split('*')
            container.extend(int(sl[0]) * [sl[1]])
    container.sort()
    return ''.join(['%d%s' % (v, k) for k, v in Counter(container).items() if k])


# 根据level排序
def sort_by_level(dt_obj, level_key):
    if not dt_obj.get(level_key):
        return [0]
    number = dt_obj[level_key].replace('A', '-').replace('B', '-').replace('C', '-').replace('D', '-').replace('E', '-')
    return [int(n) for n in number.split('-') if n]


@statistics_api.get('/')  # 部门工作统计总表 st:统计方式；ut人员方式
def get_department_statistics(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                              st: int = Query(..., ge=1, le=2), ut: int = Query(..., ge=1, le=3),
                              sort: int = Query(..., ge=1, le=14)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error('start or end invalid!')
    # 准备最后的容器
    count_container = {}

    is_admin = security.operator_is_manager(person)
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)

    db_conn = FAConnection()
    """1 获取数据表的统计数 """
    datalib_indexes = db_conn.query("""
        SELECT a.formula,b.user_id  
        FROM datalib_sheet_column As a INNER JOIN datalib_variety_sheet AS b ON a.sheet_id=b.id 
        AND a.is_diff=0 AND b.user_id<>2;
    """)  # user_id=2是开发者
    # 去除掉公式数据
    datalib_indexes = list(filter(lambda x: len(x['formula']) <= 14, datalib_indexes))
    datalib_counter = Counter([i['user_id'] for i in datalib_indexes]).items()
    # 提取个人自定义数据
    # ''.startswith('rj_R0002')
    own_indexes = list(filter(lambda x: x['formula'].startswith('rj_') and not x['formula'].startswith('rj_R0002'),
                              datalib_indexes))
    own_datalib_counter = Counter([i['user_id'] for i in own_indexes]).items()

    for ct in datalib_counter:
        if ct[0] in count_container.keys():
            count_container[ct[0]]['datalib_count'] = ct[1]
        else:
            count_container[ct[0]] = {'datalib_count': ct[1], 'datalib_own_count': 0}
    for ct in own_datalib_counter:
        if ct[0] in count_container.keys():
            user_obj = count_container[ct[0]]
            user_obj['datalib_own_count'] = ct[1]
            user_obj['datalib_own_rate'] = round(ct[1] / user_obj['datalib_count'], 4)
        # 以下不会执行，因为如果连数据库指标都没有，就不存在自采数据
        # else:
        #     count_container[ct[0]] = {
        #         'datalib_own_count': ct[1],
        #     }

    """2 获取短信通统计数 """
    db_conn = FAConnection()
    msg_records = db_conn.query("SELECT id,user_id FROM work_short_message WHERE create_date>=%s AND create_date<=%s;",
                                param=[start_date, end_date])
    short_msg_counter = Counter([i['user_id'] for i in msg_records]).items()
    for ct in short_msg_counter:
        if ct[0] in count_container.keys():
            count_container[ct[0]]['short_message_count'] = ct[1]
        else:
            count_container[ct[0]] = {'short_message_count': ct[1]}
    del msg_records
    """3 查询投顾策略的数据 """
    db_conn = FAConnection()
    close_time_start = datetime_utils.auth_datetime_string(start_date, f='%Y%m%d', rf='%Y-%m-%d 00:00:00')
    close_time_end = datetime_utils.auth_datetime_string(end_date, f='%Y%m%d', rf='%Y-%m-%d 23:59:59')
    strategy_records = db_conn.query("""
        SELECT id,creator,profit,examine_state FROM info_strategy WHERE is_delete=0 AND close_time IS NOT NULL 
        AND close_time>=%s AND close_time<=%s;
    """, param=[close_time_start, close_time_end])
    # 成功数
    strategy_success_records = list(filter(lambda x: x['profit'] > 0 and x['examine_state'] == 1, strategy_records))
    strategy_counter = Counter([i['creator'] for i in strategy_records]).items()
    strategy_success_counter = Counter([i['creator'] for i in strategy_success_records]).items()
    for ct in strategy_counter:
        if ct[0] in count_container.keys():
            count_container[ct[0]]['strategy_count'] = ct[1]
        else:
            count_container[ct[0]] = {'strategy_count': ct[1]}
    for ct in strategy_success_counter:
        if ct[0] in count_container.keys():
            user_obj = count_container[ct[0]]
            user_obj['strategy_success_count'] = ct[1]  # 成功数
            user_obj['strategy_success_rate'] = round(ct[1] / user_obj['strategy_count'], 4)
        # 以下不会执行，如果连策略数量都没有，就不存在成功率
        # else:
        #     count_container[ct[0]] = {'strategy_success_count': ct[1]}
    del strategy_records

    """4 查询投资方案"""
    db_conn = FAConnection()
    close_time_start = datetime_utils.auth_datetime_string(start_date, f='%Y%m%d', rf='%Y-%m-%d 00:00:00')
    close_time_end = datetime_utils.auth_datetime_string(end_date, f='%Y%m%d', rf='%Y-%m-%d 23:59:59')
    plan_records = db_conn.query("""
        SELECT id,creator,plan_profit,audit_flag FROM exchange_plan WHERE close_time IS NOT NULL 
        AND close_time>=%s AND close_time<=%s;
    """, param=[close_time_start, close_time_end])

    # 成功数
    plan_success_records = list(filter(lambda x: x['plan_profit'] > 0 and x['audit_flag'] == 1, plan_records))
    plan_counter = Counter([i['creator'] for i in plan_records]).items()
    plan_success_counter = Counter([i['creator'] for i in plan_success_records]).items()
    for ct in plan_counter:
        if ct[0] in count_container.keys():
            count_container[ct[0]]['investment_plan_count'] = ct[1]
        else:
            count_container[ct[0]] = {'investment_plan_count': ct[1]}
    for ct in plan_success_counter:
        if ct[0] in count_container.keys():
            user_obj = count_container[ct[0]]
            user_obj['investment_plan_success_count'] = ct[1]
            user_obj['investment_plan_success_rate'] = round(ct[1] / user_obj['investment_plan_count'], 4)
        # 以下不会执行，如果连投资方案数量都没有，更没有成功率
        # else:
        #     count_container[ct[0]] = {'investment_plan_success_count': ct[1]}
    del plan_records

    """5 查询文章数据"""
    db_conn = FAConnection()
    article_records = db_conn.query("""
        SELECT id,user_id,article_type,level_flag FROM work_article_writing 
        WHERE examine_state=1 AND create_date>=%s AND create_date<=%s;
    """, param=[start_date, end_date])

    # print('文章数量:', len(article_records))
    # 专题研究
    st_keys = {'T01': 'special_article_count', 'T02': 'wechat_article_count', 'T03': 'media_article_count'}
    for at in ['T01', 'T02', 'T03']:  # 专题、公众号、媒体
        articles = list(filter(lambda x: x['article_type'] == at, article_records))
        article_st = calculate_levels(articles, user_key='user_id', level_key='level_flag')
        for u, value in article_st.items():
            if u in count_container.keys():
                user_values: dict = count_container[u]
                user_values[st_keys[at]] = value
            else:
                count_container[u] = {st_keys[at]: value}
    del article_records
    """6 统计非常规和演讲数据"""

    db_conn = FAConnection()
    special_task_records = db_conn.query("""
        SELECT id,user_id,swiss_coin,score FROM work_special_task 
        WHERE is_examined=1 AND create_date>=%s AND create_date<=%s;
    """, param=[start_date, end_date], keep_conn=True)
    lecture_records = db_conn.query("""
        SELECT id,user_id,swiss_coin,applicant,level_flag FROM work_lecture WHERE create_date>=%s AND create_date<=%s;
    """, param=[start_date, end_date])
    # 给非常规ABCDE的评级
    for task in special_task_records:
        task['level_flag'] = constant.SCORE_LEVELS.get(task['score'], 'E')
        del task['score']
    # 将演讲培训的申请者为【营业部】的加入非常规统计中
    special_task_records.extend(list(filter(lambda x: x['applicant'] == '营业部', lecture_records)))
    # 演讲沙龙只留下其他的
    lecture_records = list(filter(lambda x: x['applicant'] != '营业部', lecture_records))
    # 统计非常规工作内容(评级和瑞币)
    special_task_dict = {}
    for task in special_task_records:
        if task['user_id'] in special_task_dict.keys():
            user_obj = special_task_dict[task['user_id']]
            user_obj['levels'].append(task['level_flag'])
            user_obj['swiss_coin'].append(task['swiss_coin'])
        else:
            special_task_dict[task['user_id']] = {
                'levels': [task['level_flag']],
                'swiss_coin': [task['swiss_coin']]
            }
    for u, value in special_task_dict.items():
        value['levels'].sort()
        level_count = Counter(value['levels']).items()
        support_task_count = ''.join(['%d%s' % (v, k) for k, v in level_count if k])
        swiss_coin = sum(value['swiss_coin'])
        # 将非常规工作内容添加入总统计中
        if u in count_container.keys():
            user_values: dict = count_container[u]
            user_values['support_task_count'] = support_task_count
            user_values['swiss_coin'] = swiss_coin
        else:
            count_container[u] = {'support_task_count': support_task_count, 'swiss_coin': swiss_coin}

    # 统计演讲沙龙(评级)
    lecture_st = calculate_levels(lecture_records, user_key='user_id', level_key='level_flag')
    for u, value in lecture_st.items():
        if u in count_container.keys():
            user_values: dict = count_container[u]
            user_values['lecture_count'] = value
        else:
            count_container[u] = {'lecture_count': value}
    # print(special_task_records)
    # print(lecture_records)
    """ 查询各分析人员的有效期 """
    db_conn = FAConnection()
    analysts = db_conn.query("SELECT id,expire,admin_name FROM ruizy_user WHERE is_analyst=1;")
    analysts_dict = {u['id']: u for u in analysts}

    """ 最后根据请求的身份，返回内容 """
    # 统计方式1：默认方式(管理员所有人,组长看小组,其他看自己)，2：小组统计方式(组长看小组,其他看自己)
    # 人员方式1：全部人员, 2：在职人员, 3：离职人员
    # print('is_admin', is_admin)
    # print(members, is_leader)

    today = datetime_utils.today(f='%Y%m%d')
    statistics_data = []
    for uid, count_values in count_container.items():
        user_obj = analysts_dict.get(uid)
        if not user_obj:
            continue
        statistics_data.append({
            'id': uid,
            'admin_name': user_obj['admin_name'],
            'expire': user_obj['expire'].strftime('%Y%m%d'),
            **count_values
        })
    if ut == 2:  # 只统计在职
        statistics_data = list(filter(lambda x: x['expire'] >= today, statistics_data))
    if ut == 3:  # 只统计离职
        statistics_data = list(filter(lambda x: x['expire'] < today, statistics_data))
    else:
        pass
    if len(statistics_data)<1:
        return AllResponse.operate_successfully(data=[])
    # 进行数据排名处理
    """
    {value: 1, text: '默认顺序'},
    {value: 2, text: '数据指标量'},
    {value: 3, text: '数据自采率'},
    {value: 4, text: '短讯通'},
    {value: 5, text: '投顾策略'},
    {value: 6, text: '策略成功率'},
    {value: 7, text: '投资方案'},
    {value: 8, text: '方案成功率'},
    {value: 9, text: '专题研究'},
    {value: 10, text: '公众号文章'},
    {value: 11, text: '媒体文章'},
    {value: 12, text: '协同支持'},
    {value: 13, text: '瑞币收入'},
    {value: 14, text: '演讲沙龙'},
    """
    if sort == 1:
        statistics_data.sort(key=lambda x: x.get('id', 0), reverse=False)
    if sort == 2:
        statistics_data.sort(key=lambda x: x.get('datalib_count', 0), reverse=True)
    elif sort == 3:
        statistics_data.sort(key=lambda x: x.get('datalib_own_rate', 0), reverse=True)
    elif sort == 4:
        statistics_data.sort(key=lambda x: x.get('short_message_count', 0), reverse=True)
    elif sort == 5:
        statistics_data.sort(key=lambda x: x.get('strategy_count', 0), reverse=True)
    elif sort == 6:
        statistics_data.sort(key=lambda x: x.get('strategy_success_rate', 0), reverse=True)
    elif sort == 7:
        statistics_data.sort(key=lambda x: x.get('investment_plan_count', 0), reverse=True)
    elif sort == 8:
        statistics_data.sort(key=lambda x: x.get('investment_plan_success_rate', 0), reverse=True)
    elif sort == 9:  # 专题研究
        statistics_data.sort(key=lambda x: sort_by_level(x, 'special_article_count'), reverse=True)
    elif sort == 10:  # 公众号文章
        statistics_data.sort(key=lambda x: sort_by_level(x, 'wechat_article_count'), reverse=True)
    elif sort == 11:  # 媒体文章
        statistics_data.sort(key=lambda x: sort_by_level(x, 'media_article_count'), reverse=True)
    elif sort == 12:  # 协同支持
        statistics_data.sort(key=lambda x: sort_by_level(x, 'support_task_count'), reverse=True)
    elif sort == 13:
        statistics_data.sort(key=lambda x: x.get('swiss_coin', 0), reverse=True)
    elif sort == 14:  # 演讲沙龙
        statistics_data.sort(key=lambda x: sort_by_level(x, 'lecture_count'), reverse=True)
    else:
        pass
    sort_keys = {
        1: 'admin_name',
        2: 'datalib_count',
        3: 'datalib_own_rate',
        4: 'short_message_count',
        5: 'strategy_count',
        6: 'strategy_success_rate',
        7: 'investment_plan_count',
        8: 'investment_plan_success_rate',
        9: 'special_article_count',
        10: 'wechat_article_count',
        11: 'media_article_count',
        12: 'support_task_count',
        13: 'swiss_coin',
        14: 'lecture_count'
    }

    # 增加排名
    statistics_data[0]['rank'] = 1
    for i, emp in enumerate(statistics_data):
        k = sort_keys.get(sort)
        if i < 1:
            continue
        cur_value, pre_value = statistics_data[i].get(k), statistics_data[i-1].get(k)
        if cur_value == pre_value:
            statistics_data[i]['rank'] = statistics_data[i-1]['rank']
        else:
            statistics_data[i]['rank'] = statistics_data[i-1]['rank'] + 1
    # 查看的数据控制
    # print(is_admin, 'st', st)
    # for s in statistics_data:
    #     print(s)
    user_count = len(statistics_data)  # 提示参与排序的人员数量
    if is_admin:  # 管理员可以统计所有人或组员
        if st == 2:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            statistics_data = list(filter(lambda x: x['id'] in members_id, statistics_data))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if st == 2 and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        statistics_data = list(filter(lambda x: x['id'] in members_id, statistics_data))
    # 添加合计
    if len(statistics_data) > 1:
        # 统计合计字段
        sum_dict = {}
        # 计算数量和
        for sk in ['datalib_count', 'datalib_own_count', 'short_message_count',
                   'strategy_count', 'strategy_success_count', 'investment_plan_count', 'investment_plan_success_count',
                   'swiss_coin']:
            sum_dict[sk] = sum([s.get(sk, 0) for s in statistics_data])
            if sum_dict[sk] == 0:
                sum_dict[sk] = None
        # 计算自采率、策略、投资方案成功率
        if sum_dict['datalib_count'] and sum_dict['datalib_own_count']:
            sum_dict['datalib_own_rate'] = round(sum_dict['datalib_own_count'] / sum_dict['datalib_count'], 4)
        if sum_dict['strategy_count'] and sum_dict['strategy_success_count']:
            sum_dict['strategy_success_rate'] = round(sum_dict['strategy_success_count'] / sum_dict['strategy_count'], 4)
        if sum_dict['investment_plan_count'] and sum_dict['investment_plan_success_count']:
            sum_dict['investment_plan_success_rate'] = round(sum_dict['investment_plan_success_count'] / sum_dict['investment_plan_count'], 4)
        # 计算评级和
        for sk in ['special_article_count', 'wechat_article_count', 'media_article_count', 'support_task_count',
                   'lecture_count']:
            sum_dict[sk] = calculate_levels_sum([s.get(sk, None) for s in statistics_data])
        statistics_data.append({
            'id': -1,
            'admin_name': '合计',
            **sum_dict
        })
    response_data = {
        'statistics_data': statistics_data,
        'title': f'研究院工作统计情况总表({start_date}-{end_date})',
        'user_count': user_count
    }
    return AllResponse.operate_successfully(data=response_data)


# 查询各个工作模块的每月数量统计
@statistics_api.get('/module/')
def department_module_statistics():

    return AllResponse.operate_successfully()
